﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IDAL;
using Model;
using Microsoft.ApplicationBlocks.Data;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class CardsDAL : ICardsDAL
    {
        private const string SQL_SELECT_CARDS = "SELECT CD_cardId, CD_cardNumber, CD_validDate, CD_cardType,CD_used FROM Cards";
        private const string SQL_SELECT_CARD = "SELECT CD_cardId, CD_cardNumber, CD_validDate, CD_cardType,CD_used FROM Cards WHERE CD_cardId = @cardId";
        private const string PARM_CARD_ID = "@cardId";
        public bool cardIdExists(int cardId)
        {
            bool isExist = false;

            try
            {
                SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True");

                SqlParameter para;
                para = new SqlParameter("@cardId", cardId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_CARDS, para))
                {
                    if (rdr.Read())
                        isExist = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isExist;
        }


        /// <summary>
        /// 根据传入的cardNumber判断一条数据是否存在
        /// </summary>       
        public bool cardNumberExists(string cardNumber)
        {
            bool isExist = false;
            string SQL_SELECT_CARDNUMBER = "SELECT CD_cardId, CD_cardNumber, CD_validDate, CD_cardType,CD_used FROM Cards WHERE CD_cardNumber = @cardNumber";

            try
            {
                SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True");

                SqlParameter para;
                para = new SqlParameter("@cardNumber", cardNumber);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_CARDNUMBER, para))
                {
                    if (rdr.Read())
                        isExist = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isExist;
        }


        /// <summary>
        /// 根据传入的cardId判断一条数据是否有效
        /// </summary>       
        public bool cardIdValiddate(int cardId)
        {
            bool isValid = false;
            string SQL_SELECT_CARDIDVALID = "SELECT CD_validDate FROM Cards WHERE CD_cardId = @cardId";

            try
            {
                SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True");

                SqlParameter para;
                para = new SqlParameter("@cardId", cardId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_CARDIDVALID, para))
                {
                    if (rdr.Read())
                    {
                        DateTime dt = System.DateTime.Now;
                        if (dt < rdr.GetDateTime(0))
                            isValid = true;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isValid;
        }


        /// <summary>
        /// 根据传入的cardNumber判断一条数据是否有效
        /// </summary>       
        public bool cardNumberValiddate(string cardNumber)
        {
            bool isValid = false;
            string SQL_SELECT_CARDNUMBERVALID = "SELECT CD_validDate FROM Cards WHERE CD_cardNumber = @cardNumber";

            try
            {
                SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True");

                SqlParameter para;
                para = new SqlParameter("@cardNumber", cardNumber);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_CARDNUMBERVALID, para))
                {
                    if (rdr.Read())
                    {
                        DateTime dt = System.DateTime.Now;
                        if (dt < rdr.GetDateTime(0))
                            isValid = true;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isValid;
        }


        /// <summary>
        /// 根据传入的cardId判断一条数据是否已被使用
        /// </summary>       
        public bool cardIdUsed(int cardId)
        {
            bool isUsed = false;
            string SQL_SELECT_CARDIDUSED = "SELECT CD_used FROM Cards WHERE CD_cardId = @cardId";

            try
            {
                SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True");

                SqlParameter para;
                para = new SqlParameter("@cardId", cardId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_CARDIDUSED, para))
                {
                    if (rdr.Read())
                    {
                        if (rdr.GetBoolean(0) == true)
                            isUsed = true;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isUsed;
        }


        /// <summary>
        /// 根据传入的cardNumber判断一条数据是否已被使用
        /// </summary>       
        public bool cardNumberUsed(string cardNumber)
        {
            bool isUsed = false;
            string SQL_SELECT_CARDNUMBERUSED = "SELECT CD_used FROM Cards WHERE CD_cardNumber = @cardNumber";

            try
            {
                SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True");

                SqlParameter para;
                para = new SqlParameter("@cardNumber", cardNumber);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_CARDNUMBERUSED, para))
                {
                    if (rdr.Read())
                    {
                        if (rdr.GetBoolean(0) == true)
                            isUsed = true;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isUsed;
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>                
        public bool Add(int cardId, string cardNumber, System.DateTime validDate, int cardType, bool used)
        {
            bool operation = false;
            string SQL_INSERT_CARD = "INSERT INTO Cards VALUES(@cardNumber, @validDate, @cardType,@used)";

            try
            {
                SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True");

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@cardNumber", cardNumber), new SqlParameter("@cardType", cardType), new SqlParameter("@validDate", validDate), new SqlParameter("@used", used) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_INSERT_CARD, paras);
                {
                    if (lineNum > 0)
                        operation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return operation;
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>                
        public bool Add(Model.Cards card)
        {
            bool operation = false;
            string SQL_INSERT_CARDS = "INSERT INTO Cards VALUES(@cardNumber, @validDate, @cardType,@used)";

            try
            {
                SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True");
                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@cardNumber", card.getCD_cardNumber()), new SqlParameter("@cardType", card.getCD_cardType()), new SqlParameter("@validDate", card.getCD_validDate()), new SqlParameter("@used", card.getCD_used()) };
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_INSERT_CARDS, paras);
                {
                    if (lineNum > 0)
                        operation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return operation;
        }


        /// <summary>
        /// 更新一条数据
        /// </summary>             
        public bool Update(int cardId, string cardNumber, System.DateTime validDate, int cardType, bool used)
        {
            bool operation = false;
            string sqlStatement = "UPDATE Cards SET CD_cardNumber = @cardNumber, CD_validDate = @validDate ,CD_cardType=@cardType,CD_used=@used WHERE CD_cardId = @cardId";

            try
            {
                SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True");

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@cardNumber", cardNumber), new SqlParameter("@validDate", validDate), new SqlParameter("@cardType", cardType), new SqlParameter("@used", used), new SqlParameter("@cardId", cardId) };

                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, paras);
                {
                    if (lineNum > 0)
                        operation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return operation;
        }


        /// <summary>
        /// 更新一条数据
        /// </summary>             
        public bool Update(Model.Cards card)
        {
            bool operation = false;
            string sqlStatement = "UPDATE Cards SET CD_cardNumber = @cardNumber, CD_validDate = @validDate ,CD_cardType=@cardType,CD_used=@used WHERE CD_cardId = @cardId";

            try
            {
                SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True");

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@cardId", card.getCD_cardId()), new SqlParameter("@cardNumber", card.getCD_cardNumber()), new SqlParameter("@validDate", card.getCD_validDate()), new SqlParameter("@cardType", card.getCD_cardType()), new SqlParameter("@used", card.getCD_used()) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, paras);
                {
                    if (lineNum > 0)
                        operation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return operation;
        }


        /// <summary>
        /// 根据传入的cardId删除一条数据
        /// </summary>
        public bool albumDelete(int cardId)
        {
            bool operation = false;
            string sqlStatement = "DELETE FROM Cards WHERE CD_cardId = @cardId";

            try
            {
                SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True");

                SqlParameter para;
                para = new SqlParameter("@cardId", cardId);

                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, para);
                {
                    if (lineNum > 0)
                        operation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return operation;
        }


        /// <summary>
        /// 根据传入的cardNumber删除一条数据
        /// </summary>
        public bool userDelete(string cardNumber)
        {
            bool operation = false;
            string sqlStatement = "DELETE FROM Cards WHERE CD_cardNumber = @cardNumber";

            try
            {
                SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True");

                SqlParameter para;
                para = new SqlParameter("@cardNumber", cardNumber);

                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, para);
                {
                    if (lineNum > 0)
                        operation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return operation;
        }


        /// <summary>
        /// 根据cardId得到一个对象实体
        /// </summary>
        public Model.Cards GetModel(int cardId)
        {
            Model.Cards card = new Model.Cards();
            string SQL_SELECT_CARDNUMBER = "SELECT * FROM Cards WHERE CD_cardId = @cardId";

            try
            {
                SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True");

                SqlParameter para;
                para = new SqlParameter("@cardId", cardId);

                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_CARDNUMBER, para))
                {
                    if (rdr.Read())
                    {
                        card.setCD_cardId(rdr.GetInt32(0));
                        card.setCD_cardNumber(rdr.GetString(1));
                        card.setCD_validDate(rdr.GetDateTime(2));
                        card.setCD_cardType(rdr.GetInt32(3));
                        card.setCD_used(rdr.GetBoolean(4));
                        card.setCD_validDate(rdr.GetDateTime(2));
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return card;
        }


        /// <summary>
        /// 根据cardNumber得到一个对象实体
        /// </summary>
        public Model.Cards GetModel(string cardNumber)
        {
            Model.Cards card = new Model.Cards();
            string SQL_SELECT_CARDNUMBER = "SELECT * FROM Cards WHERE CD_cardNumber = @cardNumber";

            try
            {
                SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True");

                SqlParameter para;
                para = new SqlParameter("@cardNumber", cardNumber);

                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_CARDNUMBER, para))
                {
                    if (rdr.Read())
                    {
                        card.setCD_cardId(rdr.GetInt32(0));
                        card.setCD_cardNumber(rdr.GetString(1));
                        card.setCD_validDate(rdr.GetDateTime(2));
                        card.setCD_cardType(rdr.GetInt32(3));
                        card.setCD_used(rdr.GetBoolean(4));
                        card.setCD_validDate(rdr.GetDateTime(2));
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return card;
        }
    }
}
